﻿using Spire.Xls;
using Spire.Xls.Core;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;

namespace ClosedXML_ExcelDemo
{
    public static class Miscellaneous
    {
        public static void InsertControl(string input, string output)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile(input);
            Worksheet ws = wb.Worksheets[0];

            ITextBoxShape textbox = ws.TextBoxes.AddTextBox(13, 1, 20, 100);
            textbox.Text = "Hello World";

            ICheckBox checkbox = ws.CheckBoxes.AddCheckBox(15, 1, 15, 100);
            checkbox.Text = "Check me";
            checkbox.CheckState = CheckState.Checked;

            IRadioButton radioButton = ws.RadioButtons.Add(17, 1, 15, 100);
            radioButton.Text = "Check me";
            radioButton.CheckState = CheckState.Checked;

            wb.SaveToFile(output);
        }

        public static void InsertObject(string input, string output)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile(input);
            Worksheet ws = wb.Worksheets[0];

            ws.OleObjects.Add("../../Documents/Document.pdf", Image.FromFile("../../Documents/PDF.png"), OleLinkType.Embed);

            wb.SaveToFile(output);
        }

        public static void SetViewMode(string input, string output)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile(input);

            Worksheet ws = wb.Worksheets[0];
            ws.ViewMode = ViewMode.Normal;

            ws = wb.Worksheets[1];
            ws.ViewMode = ViewMode.Layout;

            ws = wb.Worksheets[2];
            ws.ViewMode = ViewMode.Preview;

            wb.SaveToFile(output);
        }

        public static void ExportDataTable(string input, string output)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile(input);
            Worksheet ws = wb.Worksheets[0];

            DataTable dt = ws.ExportDataTable();
            StringBuilder sb = new StringBuilder();

            foreach (DataRow row in dt.Rows)
            {
                foreach (DataColumn column in dt.Columns)
                {
                    sb.Append(row[column.ColumnName].ToString() + ",");
                }
                sb.AppendLine();
            }

            File.WriteAllText(output, sb.ToString(), Encoding.UTF8);
        }

        public static void ZoomWorkSheet(string input, string output)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile(input);

            Worksheet ws = wb.Worksheets[0];
            ws.ZoomScaleNormal = 50;

            ws = wb.Worksheets[1];
            ws.ZoomScalePageLayoutView = 50;

            ws = wb.Worksheets[2];
            ws.ZoomScalePageBreakView = 50;

            wb.SaveToFile(output);
        }
    }
}